﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using BulletinModel;
using System.Web.Mvc;
using BulletinMvc.Models;
using System.Data.Entity;
using System.Data;
using System.IO;
using System.Data.OleDb;
 using Webdiyer.WebControls.Mvc;
namespace BulletinMvc.Lib
{
    public class BulletinItemBL:IBulletinItemBL
    {
        private BulletinDBEntities db;
        private ModelStateDictionary _errorLog;

        public BulletinItemBL(ModelStateDictionary modelState)        
        {
            _errorLog = modelState;
            db = new BulletinDBEntities();
        
        }

        public IQueryable<BulletinItem> GetBulletinItemList(int BulletinID) 
        {
           // return db.BulletinItems.Where(a=>a.BulletinID==BulletinID);
            return from a in db.BulletinItems where a.BulletinID == BulletinID orderby a.BulletinID descending select a;
        }
       public  bool DeleteBulletinItem(string ProjectSN) 
        {
            try
            {
                BulletinItem bulletinitem = db.BulletinItems.Find(ProjectSN);
                db.BulletinItems.Remove(bulletinitem);
                db.SaveChanges();
                return true;
            }
            catch {

                return false;
            }   
        }

       public BulletinItemCreateModel GetBulletinItemDetail(string ProjectSN) 
       {
           var bulletinitem = from n in db.BulletinItems
                              where n.ProjectSN == ProjectSN
                              select new BulletinItemCreateModel
                              {
                                  ProjectSN = n.ProjectSN,
                                  ProjectTitle = n.ProjectTitle,
                                  Country = n.County,
                                  School = n.School,
                                  Teachername = n.TeacherName,
                                  Conclusion = n.Conclusion,
                                  Comment = n.Comment
                              };
           return bulletinitem.SingleOrDefault();
       
       }

       public void saveBulletinItem(BulletinItemCreateModel savebulletinItem, int BulletinID) 
        {
            BulletinItem bulletinItem = new BulletinItem
            {
                 ProjectSN=savebulletinItem.ProjectSN,
                 ProjectTitle = savebulletinItem.ProjectTitle,
                  County=savebulletinItem.Country,
                   School=savebulletinItem.School,
                    TeacherName=savebulletinItem.Teachername,
                     Conclusion=savebulletinItem.Conclusion,
                      Comment=savebulletinItem.Comment,
                  BulletinID = BulletinID
                   
                       
            };
            db.BulletinItems.Attach(bulletinItem);
            db.Entry(bulletinItem).State = EntityState.Modified;
            db.SaveChanges();
             
        }
       public void AddBulletinItem(BulletinItemCreateModel savebulletinItem, int BulletinID) 
       {
           BulletinItem bulletinItem = new BulletinItem
           {
               ProjectSN = savebulletinItem.ProjectSN,
               ProjectTitle = savebulletinItem.ProjectTitle,
               County = savebulletinItem.Country,
               School = savebulletinItem.School,
               TeacherName = savebulletinItem.Teachername,
               Conclusion = savebulletinItem.Conclusion,
               Comment = savebulletinItem.Comment,
               BulletinID = BulletinID
           };
            db.BulletinItems.Add(bulletinItem);
         //   db.Entry(bulletinItem).State = EntityState.Modified;
            db.SaveChanges();     
       }
       public bool ExitProjectSN(string ProjectSN) 
       {

           if (db.BulletinItems.Where(a => a.ProjectSN == ProjectSN).Count() > 0)
               return true;
               return false;   
       }
        //2种修改方式
        //        Attendence addstudentattendence = new Attendence
        //{
        //    ID = model.attendenceid,
        //    ClassId = model.classid,
        //    StudentId = (from b in Db.Students where b.Xuehao == model.xuehao && b.Name == model.name select b.ID).FirstOrDefault(),
        //    Date = model.date,
        //    Issick = true,
        //    Showup = false,
        //    Reason = model.reason,
        //};
        //Db.Attendence.Attach(addstudentattendence);
        //Db.Entry(addstudentattendence).Property(x => x.Reason).IsModified = true;
        //Db.Entry(addstudentattendence).Property(x => x.Date).IsModified = true;
        //Db.SaveChanges();
        //var script = String.Format("<script>alert('修改成功!');location.href='{0}'</script>", Url.Action("Index", "Stuaddleave", new { classid = model.classid, teacherid = teacherId }));
        //return Content(script, "text/html");

        //        db.Entry(album).State = EntityState.Modified;
        //db.SaveChanges();

       public bool saveExcelBulletinItems (string filePath ,string saveName,int BulletinID)
      
      {
          //List<addmembershipstudents> objectmembershipstudent = new List<addmembershipstudents>();
          //List<addstudenttostudent> objectstudents = new List<addstudenttostudent>();
          try
          {
              var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(filePath, saveName) + ";Extended Properties=Excel 12.0;");
              //Fill the dataset with information from the BV and REV worksheet.
              var adapterdata = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
              var dt = new DataSet();
              adapterdata.Fill(dt);
              DataTable dataBulletinitems = dt.Tables[0];
              DataView dv = new DataView(dataBulletinitems);
              if (dv.Count != dv.ToTable(true, "项目编号").Rows.Count)
              {
                  //var script = String.Format("<script>alert('excel表中学号有重复！');location.href='{0}'</script>", Url.Action("studentsmanagent", "teacher"));
                  //return Content(script, "text/html");
                  _errorLog.AddModelError("", "excel表中项目编号有重复！");
                   return false;
              }
              //    List<students> objectstudentEntity = new List<students>();
              //临时对象保存数据


              //添加到 objectstudentEntity数据集中
              for (int i = 0; i < dataBulletinitems.Rows.Count; )
              {
                  string ExcelProjectSN = dataBulletinitems.Rows[i]["项目编号"].ToString().Trim();
                  string ExcelProjectTitle = dataBulletinitems.Rows[i]["项目名称"].ToString().Trim();
                  string excelCountry = dataBulletinitems.Rows[i]["区县"].ToString().Trim();
                  string excelSchool = dataBulletinitems.Rows[i]["单位"].ToString().Trim();
                  string excelTeachername = dataBulletinitems.Rows[i]["姓名"].ToString().Trim();
                  string excelComment = dataBulletinitems.Rows[i]["评语"].ToString().Trim();
                  string excelConclusion = dataBulletinitems.Rows[i]["结果"].ToString().Trim();
                  var ExistProjectSN = from v in db.BulletinItems where v.ProjectSN == ExcelProjectSN select v;
                  if (ExistProjectSN.Count() <= 0)
                  {
                      //添加到数据库中
                      BulletinItem bulletinItem = new BulletinItem 
                      {
                          ProjectSN = ExcelProjectSN,
                          ProjectTitle = ExcelProjectTitle,
                           County=excelCountry,
                            School=excelSchool,
                             TeacherName=excelTeachername,
                              Comment=excelComment,
                               Conclusion=excelConclusion,
                             BulletinID = BulletinID                                      
                      };
                      db.BulletinItems.Add(bulletinItem);
                      db.SaveChanges();
                      i++;
                  }
                  else if (ExistProjectSN.Count() > 0)
                  {
                      // i++;
                      _errorLog.AddModelError("", "excel表中有与系统重复的项目编号,操作失败！");
                      return false;

                  }

              }
          }
          catch
          {
              //var script = String.Format("<script>alert('数据读取出错!');location.href='{0}'</script>", Url.Action("studentsmanagent", "teacher"));
              //return Content(script, "text/html");
              _errorLog.AddModelError("", "数据读取出错!");
              return false;
          }
          return _errorLog.IsValid;
      }
//获取details view list
       public PagedList<BulletinItem> GetDetailsViewList(int BulletinID, int pagesize = 10, int id = 1)
       {

           //BulletinItemViewModel model = new BulletinItemViewModel();
           //model.BulletinItemList = GetBulletinItemList(BulletinID);
           PagedList<BulletinItem> BulletinItemList = GetBulletinItemList(BulletinID).ToPagedList(id, pagesize);
           return BulletinItemList;
       }

       public int GetBulletinItemCount(int BulletinID) 
       {
           return db.BulletinItems.Where(a => a.BulletinID == BulletinID).Count();
             
       }
    }


}




